﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Table_Dal;
using DAL;
namespace DAL
{
    public class MonHocDAL:DataAccessLayer
    {
        DataDataContext dt;
        public List<MonHocTbl> LayTatCaMonHoc()
        {
            dt = GetKetnoi();
            List<MonHocTbl> DSMonHoc = new List<MonHocTbl>();
            IEnumerable<MONHOC> ds = dt.MONHOCs;
            foreach (MONHOC item in ds)
            {
                MonHocTbl MonHocMoi = new MonHocTbl();
                MonHocMoi.MaMonHoc = item.MAMONHOC;
                MonHocMoi.TenMonHoc = item.TENMONHOC;
                DSMonHoc.Add(MonHocMoi);
            }
            return DSMonHoc;
        }
        public bool KiemTra_TrungKhoa(string Ma)
        {
            dt = GetKetnoi();
            IEnumerable<MONHOC> q = dt.MONHOCs.Where(k => k.MAMONHOC== Ma);
            foreach (MONHOC item in q)
            {
                if (item.MAMONHOC == Ma)
                    return true;
            }
            return false;
        }
        public bool SuaMonHoc(MonHocTbl monhoc)
        {
            dt = GetKetnoi();
            MONHOC mh = dt.MONHOCs.SingleOrDefault(k => k.MAMONHOC == monhoc.MaMonHoc);
            mh.TENMONHOC = monhoc.TenMonHoc;
            dt.SubmitChanges();
            return true;
        }
        public bool ThemMonHoc(MonHocTbl monhoc)
        {
            try
            {
                dt = GetKetnoi();
                MONHOC mh = new MONHOC();
                mh.MAMONHOC = monhoc.MaMonHoc;
                mh.TENMONHOC = monhoc.TenMonHoc;
                dt.MONHOCs.InsertOnSubmit(mh);
                dt.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
         }
        public bool XoaMonHoc(string MaMon)
        {
            try
            {
                dt = GetKetnoi();
                MONHOC mh = dt.MONHOCs.SingleOrDefault(k => k.MAMONHOC == MaMon);
                dt.MONHOCs.DeleteOnSubmit(mh);
                dt.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
        }
        public List<MonHocTbl> LayMonHoc_MaDethi(string mssv)
        {
            dt = GetKetnoi();
            IEnumerable<MONHOC> q = (from i in dt.MONHOCs
                                     join u in dt.CHUONGs on i.MAMONHOC equals u.MAMONHOC
                                    join j in dt.CAUHOIs on u.MACHUONG equals j.MACHUONG
                                    join z in dt.CHITIETDETHIs on j.MACAUHOI equals z.MACAUHOI
                                    join x in dt.DETHIs on z.MADETHI equals x.MADETHI
                                    join y in dt.DIEMs on x.MADETHI equals y.MADETHI
                                     where y.MSSV == mssv && y.DIEM1 ==-1
                                    select i).Distinct();
            List<MonHocTbl> ds = new List<MonHocTbl>();
            foreach ( MONHOC item in q)
            { 
                MonHocTbl mh = new MonHocTbl();
                mh.MaMonHoc = item.MAMONHOC;
                mh.TenMonHoc = item.TENMONHOC;
                ds.Add(mh);
            }
            return ds;
        }
        public MonHocTbl LayMonHoc_TheoMaDethi(string made)
        {
            dt = GetKetnoi();
            MONHOC q = (from i in dt.MONHOCs
                                     join u in dt.CHUONGs on i.MAMONHOC equals u.MAMONHOC
                                     join j in dt.CAUHOIs on u.MACHUONG equals j.MACHUONG
                                     join z in dt.CHITIETDETHIs on j.MACAUHOI equals z.MACAUHOI
                                     join x in dt.DETHIs on z.MADETHI equals x.MADETHI
                                     where x.MADETHI==made
                                     select i).Distinct().Single();
            MonHocTbl mh = new MonHocTbl();
                mh.MaMonHoc = q.MAMONHOC;
                mh.TenMonHoc = q.TENMONHOC;
                
            return mh;
        }
    }
}
